package com.hui.dao;

import com.hui.dao.ClassDao;
import com.hui.entity.Class;
import com.hui.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Auther: 李昭辉
 * @Date: 2021/07/09/14:04
 * @Description: 班级类 数据库访问层实现类
 */
public class ClassDaoImpl implements ClassDao {
    @Override
    public List<Class> getAllClass() {
        List<Class> list = new ArrayList<>();
        Connection con = DruidUtil.getConnection();
        String sql = "SELECT * FROM class ";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String className = resultSet.getString("class_name");
                String grade = resultSet.getString("grade");
                String teacher = resultSet.getString("teacher");
                String slogan = resultSet.getString("slogan");
                int total = resultSet.getInt("total");
                list.add(new Class(id, className, grade, teacher, slogan, total));
            }
            DruidUtil.close(con, ps, resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Class> getClassById(int id) {
        List<Class> list = new ArrayList<>();
        Connection con = DruidUtil.getConnection();
        String sql = "SELECT * FROM class WHERE id = ? ";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String className = resultSet.getString("class_name");
                String grade = resultSet.getString("grade");
                String teacher = resultSet.getString("teacher");
                String slogan = resultSet.getString("slogan");
                int total = resultSet.getInt("total");
                list.add(new Class(id, className, grade, teacher, slogan, total));
            }
            DruidUtil.close(con, ps, resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getClassTotal(int id) {
        String sql = "SELECT total FROM class WHERE id = ?";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet resultSet = ps.executeQuery();
            resultSet.next();
            int total = resultSet.getInt("total");
            DruidUtil.close(con, ps, resultSet);
            return total;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public boolean delClass(int id) {
        String sql = "DELETE FROM class WHERE id = ?";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            int i = ps.executeUpdate();
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean addClass(Class c) {
        // 班级添加默认为0人
        String sql = "INSERT INTO class VALUES (NULL,?,?,?,?,0)";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, c.getClassName());
            ps.setString(2, c.getGrade());
            ps.setString(3, c.getTeacher());
            ps.setString(4, c.getSlogan());
            int i = ps.executeUpdate();
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    // 修改班级的基本信息
    @Override
    public boolean updateClass(Class c) {
        String sql = "UPDATE class SET class_name=?,grade=?,teacher=?,slogan=? WHERE id=?";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, c.getClassName());
            ps.setString(2, c.getGrade());
            ps.setString(3, c.getTeacher());
            ps.setString(4, c.getSlogan());
            ps.setInt(5, c.getId());
            int i = ps.executeUpdate();
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean plusOne(int id) {
        String sql = "UPDATE class SET total=total+1 WHERE id=?";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            int i = ps.executeUpdate();
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean minusOne(int id) {
        String sql = "UPDATE class SET total=total-1 WHERE id=?";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            int i = ps.executeUpdate();
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    // 根据年级获取班级数量
    @Override
    public int getCountByClassAndGrade(String className, String grade) {
        String sql = "SELECT COUNT(*) AS num FROM class WHERE class_name=? AND grade= ?";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, className);
            ps.setString(2, grade);
            ResultSet resultSet = ps.executeQuery();
            resultSet.next();
            int num = resultSet.getInt("num");
            DruidUtil.close(con, ps, resultSet);
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public boolean exitClass(int classId) {
        String sql = "SELECT COUNT(*) AS num FROM class WHERE id = ?";
        Connection con = DruidUtil.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, classId);
            ResultSet resultSet = ps.executeQuery();
            resultSet.next();
            int num = resultSet.getInt("num");
            DruidUtil.close(con, ps, resultSet);
            return num > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
